package com.example.newleadcent.openplant.dao;

import com.magus.OPException;

import java.sql.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;

/**
 * 数据库操作基础类
 */
public class Base_dao {

    public static String className = "com.magus.jdbc.Driver";
    // jdbc:产品系列://IP:端口/服务
    // jdbc:产品系列://IP:端口/
    public static String url = "jdbc:openplant://114.115.232.206:8200/W3";
    public static String user = "sis";
    public static String password = "openplant";

    private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    private static DecimalFormat df = new DecimalFormat("#.###");

    public static boolean testConnection() {
        try {
            Connection conn = initConnection();
            System.out.println(conn);
            if (conn != null) {
                conn.close();
                return true;
            } else {
                return false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (OPException e) {
            e.printStackTrace();
        }
        return false;
    }

    public static Connection initConnection() throws OPException {
        try {
            Class.forName(className);
            return DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (SecurityException e) {
            OPException ex = new OPException("程序包空间不能与提供的JAR包空间一致！");
            throw ex;
        }
        return null;
    }

    protected static void executeSQL(String sql) {
        try {
            System.out.println("sql:" + sql);
            Connection conn = initConnection();
            Statement st = conn.createStatement();
            boolean success = st.execute(sql);
            if (success) {
                System.out.println("操作成功");
            } else {
                System.out.println("error");
            }
            st.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    protected static void executeSelectSQL(String sql) {
        try {
            System.out.println("sql:" + sql);
            Connection conn = initConnection();

            Statement st = conn.createStatement();

            ResultSet rs = st.executeQuery(sql);
            ResultSetMetaData rsmd = rs.getMetaData();
            String formatStr = "%-20s";
            for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                System.out.println(rsmd.getColumnType(i));
                System.err.print(String.format(formatStr, rsmd.getColumnLabel(i).toUpperCase()) + "\t");
            }
            System.err.print("\n");
            Thread.sleep(100);
            while (rs.next()) {
                StringBuffer sb = new StringBuffer();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    if (rsmd.getColumnType(i) == Types.TIMESTAMP) {
                        // sb.append(sdf.format(rs.getDate(i)) + "\t");

                        sb.append(sdf.format(rs.getDate(i)) + "\t");

                        sb.append((rs.getInt(i)) + "\t");

                        sb.append((rs.getLong(i)) + "\t");

                        sb.append((rs.getString(i)) + "\t");

                        sb.append(sdf.format(rs.getTimestamp(i)) + "\t");

                    } else if (rsmd.getColumnType(i) == Types.BOOLEAN) {
                        sb.append(rs.getBoolean(i) + "\t");
                    } else {
                        sb.append(String.format(formatStr, rs.getString(i)) + "\t");
                    }
                }
                System.out.println(sb.toString());
            }

            rs.close();

            st.close();

            conn.close();
        } catch (Exception e) {
        }
    }
}

